package org.wedding.center.util;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;

/**
 * 新建mysql数据库表工具类
 * 将xlsx文件中的表结构生成sql语句
 *
 * @author songkaiyue
 * @date 2025-05-06 14:56:03
 */
public class ExcelToSqlUtil {
    public static void main(String[] args) {
//        String excelFilePath = "C:\\Users\\Administrator\\Desktop\\testTable.xlsx"; // Excel 文件路径
        String excelFilePath = "E:\\workSpace\\bykj\\需求\\售课系统\\售课系统数据库设计.xlsx"; // Excel 文件路径
        String outputFilePath = "C:\\Users\\17928\\Desktop\\testTable.sql"; // 输出文本文件路径

        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fis);
             FileWriter writer = new FileWriter(outputFilePath)) {
            StringBuilder sql = new StringBuilder();
            int i = 0;
            for (Sheet sheet : workbook) {
                i++;
                // 跳过前两个表
                if (i < 2) continue;

                //中文表名
                String tableNameC = sheet.getRow(1).getCell(1).getStringCellValue();
                //英文表名
                String tableNameE = sheet.getRow(2).getCell(1).getStringCellValue();

                // 创建 SQL 插入语句
                // 遍历 Excel 行
                sql.append("SET NAMES utf8mb4;\n").append("SET FOREIGN_KEY_CHECKS = 0;\n");
                sql.append("DROP TABLE IF EXISTS `").append(tableNameE).append("`;").append("\n");
                sql.append("CREATE TABLE `").append(tableNameE).append("`  (\n").append("  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标识',").append("\n");
                for (Row row : sheet) {
                    // 跳过表头
                    if (row.getRowNum() < 8) continue;

                    // 读取单元格数据
                    //英文字段名
                    String fieldE = row.getCell(1).getStringCellValue();
                    if (StrUtil.isBlank(fieldE)) continue;
                    //中文字段名
                    String fieldC = row.getCell(2).getStringCellValue();
                    //字段类型
                    String type = row.getCell(3).getStringCellValue();
                    //备注
                    if (ObjectUtil.isNotEmpty(row) && ObjectUtil.isNotEmpty(row.getCell(5))) {
                        Cell cell = row.getCell(5);
                        if (cell.getCellType() == CellType.NUMERIC) {
                            cell.setCellType(CellType.STRING);
                        }
                        String remark = cell.getStringCellValue();
//                        if (StrUtil.isNotBlank(remark)&&StrUtil.isNotEmpty(remark)) fieldC += " " + remark;
                    }
                    //滤过删除标识字段
                    if ("del_flag".equals(fieldE) || "id".equals(fieldE)) {
                        continue;
                    }
                    if (type.contains("decimal")){
                        sql.append("`").append(fieldE).append("` decimal(6, 2) UNSIGNED ZEROFILL NULL DEFAULT NULL COMMENT '").append(fieldC).append("',\n");
                    }else {
                        sql.append("`").append(fieldE).append("` ").append(type).append(" CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '").append(fieldC).append("',\n");
                    }
                }
                sql.append("  `del_flag` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '删除标识：0:未删除  1:已删除',\n").append("  PRIMARY KEY (`id`) USING BTREE\n").append(") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '").append(tableNameC).append("' ROW_FORMAT = Dynamic;\n").append("\n").append("SET FOREIGN_KEY_CHECKS = 1;\n\n\n");
            }
            // 写入到文本文件
            writer.write(sql.toString());

        } catch (IOException e) {
            throw new RuntimeException("Error processing the Excel file", e);
        }
    }
}
